package com.jintoufs.util.poi;

import com.jintoufs.util.date.DateOperator;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.cosmos.modules.exception.ServiceException;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.Date;

/**
 * Created by Administrator on 2015/7/1.
 */
public class ExcelUtils {
    public static String getCellStrValue(Cell cell) {
        return getCellStrValue(cell, null);
    }

    public static String getCellStrValue(Cell cell, Class type) {
        if (cell == null) return null;
        String ret;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                ret = null;
                break;
            case Cell.CELL_TYPE_FORMULA:
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                try {
                    ret = getCellStrValue(evaluator.evaluateInCell(cell), type);
                } catch (Exception e) {
                    throw new ServiceException(cell.getSheet().getSheetName() + (cell.getRow().getRowNum() + 1) + "行" + (cell.getColumnIndex() + 1) + "列，公式解析错误：" + e.getMessage());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (PoiDateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    ret = DateOperator.formatDate(theDate);
                } else if (type != null && type.equals(Date.class)) {
                    double value = cell.getNumericCellValue();
                    Date theDate = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    ret = DateOperator.formatDate(theDate);
                } else {
                    ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = null;
        }

        return ret == null ? null : ret.trim(); //有必要自行trim
    }

    /**
     * 检测是否是excel文件
     *
     * @param inputStream
     * @return
     * @throws IOException
     */
    public static boolean isExcelFile(InputStream inputStream) throws IOException {
        boolean flag = false;
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inputStream) || POIXMLDocument.hasOOXMLHeader(inputStream)) {
            flag = true;
        }
        return flag;
    }

    /**
     * 获取Excel处理类
     *
     * @param inputStream
     * @return
     * @throws IOException
     */
    public static PoiExcelHelper getPoiExcelHelper(InputStream inputStream) throws IOException {
        PoiExcelHelper helper = null;
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
            helper = new PoiExcel2k7Helper();
        }
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            helper = new PoiExcel2k3Helper();
        }
        return helper;
    }
}
